"""监督-柜子"""
from datetime import datetime, timedelta
import base64
from loguru import logger
import httpx
import orjson
from flask import g, current_app
import pytz
from psycopg2 import extras
from .kh_api import handle_auth


def inspect_handle_cabinet(employee_id, qrcode_base64):
    """分配柜子"""
    inspect_cabinet_status()
    box = inspect_find_free_cabinet()
    if box is None:
        return None, "柜子已满"
    res = inspect_assign_cabinet(employee_id, qrcode_base64, box)
    if 'errorCode' not in res:
        update_expert_info(employee_id, box['id'])
    else:
        with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
            query = """
                SELECT 
                    "position", "row", "column", "position_id", ip_address, cabinet.id
                FROM 
                    cabinet
                JOIN 
                    persons ON cabinet.id = persons.box_id
                WHERE
                    persons.id_card = %s
            """
            cursor.execute(query, (employee_id, ))
            box = cursor.fetchone()

    logger.bind(log_type='signin').info({"id_card": employee_id, "action": "下发人员信息及权限", "data": res})
    return box, None


def update_expert_info(employee_id, box_id):
    """更新专家信息"""
    with g.db_conn.cursor() as cursor:
        query = "UPDATE persons SET box_id = %s WHERE id_card = %s AND deleted_at IS NULL"
        cursor.execute(query, (box_id, employee_id))
        g.db_conn.commit()


def inspect_assign_cabinet(employee_id, qrcode_base64, box):
    """下发人员信息及权限"""
    url = f"http://{box.get('ip_address')}/ISAPI/AccessControl/userInfoAndRight/setup?format=json"
    begin_time = (datetime.now(pytz.timezone('Asia/Shanghai')) - timedelta(seconds=60 * 30)).strftime("%Y-%m-%dT%H:%M:%S")
    end_time = (datetime.now(pytz.timezone('Asia/Shanghai')) + timedelta(days=15)).strftime("%Y-%m-%dT%H:%M:%S")
    params = {
        "UserInfoAndRight": {
            "employeeNo": employee_id,
            "deleteUser": False,
            "name": "",
            "userType": "normal",
            "localUIRight": False,
            "operationType": "add",
            "userVerifyMode": "",
            "Valid": {
                "enable": True,
                "beginTime": begin_time,
                "endTime": end_time
            },
            "IntelligentCabinet": {
                "deleteAllCabinet": True,
                "List": [{
                    "position": box['position'],
                    "positionID": box['position_id'],
                    "rightType": "part",
                    "deleteCabinet": False,
                    "PartInfo": {
                        "List": [{
                            "row": box['row'],
                            "column": box['column'],
                            "storageTimes": 4294967295,
                            "retrievalTimes": 4294967295,
                            "deleteSpace": False,
                            "Valid": {
                                "enable": True,
                                "beginTime": "2023-11-23T00:00:00",
                                "endTime": "2033-11-22T23:59:59"
                            }
                        }]
                    }
                }
                ]
            },
            "CardInfo": {
                "deleteAllCard": True,
                "List": []
            },
            "FaceInfo": {
                "deleteAllFace": True,
                "List": [{
                    "FDID": "1",
                    "FPID": employee_id,
                    "deleteFace": False
                }]
            }
        }
    }
    payload = {"userInfo": orjson.dumps(params)}
    binary_image = base64.b64decode(qrcode_base64)
    files = [
        ('img', ('file.jpg', binary_image, 'image/jpeg'))
    ]

    auth = handle_auth(url)
    return httpx.post(url, auth=auth, data=payload, files=files).json()


def inspect_find_free_cabinet():
    """查询一个空闲的柜子"""
    
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        query = """
            SELECT 
                boxid, "position", position_id, "row", "column", ip_address, id 
            FROM cabinet 
            WHERE 
                space_status = 'empty' AND access_status = 0 AND lock_status = 0 
                AND exception_status = 0 AND openclose_status = 'close' 
                AND position = 'M' 
                AND (updated_at IS NULL OR updated_at < %s)
            ORDER BY 
                RANDOM()
            LIMIT 1
        """
        cursor.execute(query, [
            int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp()) - (60 * current_app.config['CABINET_EXPIRES'])])
        return cursor.fetchone()


def inspect_cabinet_status():
    """更新柜子的状态"""
  
    exists_query = """SELECT EXISTS(SELECT 1 FROM cabinet WHERE "position" = %s AND position_id = %s AND "row" = %s AND "column" = %s AND ip_address = %s)"""

    insert_query = """
        INSERT INTO 
            cabinet("position", position_id, name, type, boxid, 
            "row", "column", space_status, access_status, lock_status, 
            exception_status, openclose_status, assignable, ip_address) 
        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    
    update_query = """
        UPDATE 
            cabinet 
        SET 
            name = %s, type = %s, space_status = %s, access_status = %s, lock_status = %s, exception_status = %s, openclose_status = %s, assignable = %s 
        WHERE 
            "position" = %s AND position_id = %s AND "row" = %s AND "column" = %s AND ip_address = %s
    """

    # cabinet = {'192.168.100.142', '192.168.100.143', '192.168.100.144'}
    # cabinet = {'192.168.100.143', '192.168.100.144'}
    cabinet = {'192.168.100.144'}
    with g.db_conn.cursor() as cursor:
        for ip_address in cabinet:

            url = f'http://{ip_address}/ISAPI/IntelligentCabinet/cabinetStatus?format=json'
            payload = {"searchID": "ce76f212-e3b2-49e3-8dd2-6a3f6aa1", "searchResultPosition": 0, "maxResults": 1, "CabinetInfo": [{"position": "M", "positionID": 1}]}
            auth = handle_auth(url)
            res = httpx.post(url, auth=auth, data=orjson.dumps(payload)).json()
            
            for item in res["CabinetStatus"]:
                for box in item["List"]:

                    boxid = f"{item['position']}0{item['positionID']}0{box['column']}0{box['row']}"

                    exists_params = (
                        item["position"],
                        item["positionID"],
                        box["row"],
                        box["column"],
                        ip_address
                    )
                    cursor.execute(exists_query, exists_params)
                    exists_cabinet = cursor.fetchone()[0]

                    if not exists_cabinet:
                        params = (
                            item["position"],
                            item["positionID"],
                            item["name"],
                            item["type"],
                            boxid,
                            box["row"],
                            box["column"],
                            box["spaceStatus"],
                            int(box["accessStatus"]),
                            int(box["lockStatus"]),
                            int(box["exceptionStatus"]),
                            box["openCloseStatus"],
                            int(box["assignable"]),
                            ip_address
                        )
                        cursor.execute(insert_query, params)
                    else:
                        params = (
                            item["name"],
                            item["type"],
                            box["spaceStatus"],
                            int(box["accessStatus"]),
                            int(box["lockStatus"]),
                            int(box["exceptionStatus"]),
                            box["openCloseStatus"],
                            int(box["assignable"]),
                            item["position"],
                            item["positionID"],
                            box["row"],
                            box["column"],
                            ip_address
                        )
                        cursor.execute(update_query, params)
                g.db_conn.commit()
